﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class SectionEmpMsgtbDAO
    {
        //将部门员工短消息存入数据库中
        public int savaShortMessage(string inceptUserLoginName, string sendUserLoginName,
            string msgContent, DateTime sendDateTime)
        {
            int count = 0;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                string sql = "insert into SectionEmpMsgtb values(@inceptUserLoginName, @sendUserLoginName, @msgContent" +
                    ", @SendDateTime, default)";
                SqlCommand sqlCmd = new SqlCommand(sql, conn);
                sqlCmd.Parameters.Add(new SqlParameter("@inceptUserLoginName", inceptUserLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@sendUserLoginName", sendUserLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@msgContent", msgContent));
                sqlCmd.Parameters.Add(new SqlParameter("@SendDateTime", sendDateTime));
                sqlCmd.ExecuteNonQuery();
                sql = "insert into SectionEmpMsgtb2 values(@iuln, @suln, @mc" +
                  ", @sdt, default)";
                sqlCmd = new SqlCommand(sql, conn);
                sqlCmd.Parameters.Add(new SqlParameter("@iuln", inceptUserLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@suln", sendUserLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@mc", msgContent));
                sqlCmd.Parameters.Add(new SqlParameter("@sdt", sendDateTime));
                sqlCmd.ExecuteNonQuery();
                sql = "select @@identity";
                sqlCmd = new SqlCommand(sql, conn);
                count = int.Parse(sqlCmd.ExecuteScalar().ToString());
            }
            catch(Exception ex)
            {
                StreamWriter sw = File.AppendText("c:\\log.txt");
                sw.WriteLine(ex.Message);
                sw.Close();
            }
            finally
            {
                if(conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return count;
        }

        //根据接收人的登录名查询收件箱
        public List<SectionEmpMsgtb> findByCurUserLoginName(string uln)
        {
            //UserDAO userDAO = new UserDAO();
            List<SectionEmpMsgtb> list = new List<SectionEmpMsgtb>();
            SqlConnection conn = null;
            SqlDataReader dr = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                //string sql = "select * from SectionEmpMsgtb where InceptUserLoginName = @uln order by SendDateTime desc";
                string sql = "select SectionEmpMsgtb2.*, usertb.username as 发送人" +
                     " from SectionEmpMsgtb2,usertb where InceptUserLoginName = @uln and usertb.loginname = sectionEmpMsgtb2.SendUserLoginName" +
                     " order by SendDateTime desc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@uln", uln));
                dr = cmd.ExecuteReader();
                while(dr.Read())
                {
                    SectionEmpMsgtb sectionEmpMsg = new SectionEmpMsgtb();
                    sectionEmpMsg.EmpMsgId = (int) dr["EmpMsgId"];
                    //sectionEmpMsg.InceptUser = userDAO.FindByLoginName(dr["InceptUserLoginName"].ToString());
                    //sectionEmpMsg.SendUser = userDAO.FindByLoginName(dr["SendUserLoginName"].ToString());
                    sectionEmpMsg.SendUserName = (string) dr["发送人"];
                    sectionEmpMsg.MsgContent = dr["MsgContent"].ToString();
                    sectionEmpMsg.ReadState = (int) dr["ReadState"];
                    sectionEmpMsg.SendDateTime = (DateTime) dr["SendDateTime"];
                    sectionEmpMsg.InceptUserName = "-";
                    list.Add(sectionEmpMsg);
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if(dr != null)
                {
                    dr.Close();
                }
                if(conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return list;
        }

        //根据消息编号查看消息内容
        public SectionEmpMsgtb findByMsgId(int mid, string st)
        {
            SectionEmpMsgtb sectionEmpMsg = new SectionEmpMsgtb();
            SqlConnection conn = null;
            SqlDataReader dr = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                UserDAO userDAO = new UserDAO();
                string sql = "select * from SectionEmpMsgtb where EmpMsgId = @mid";
                if("incept".Equals(st))
                {
                    sql = "select * from SectionEmpMsgtb2 where EmpMsgId = @mid";
                }
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@mid", mid));
                dr = cmd.ExecuteReader();
                while(dr.Read())
                {
                    sectionEmpMsg.EmpMsgId = (int) dr["EmpMsgId"];
                    sectionEmpMsg.InceptUser = userDAO.FindByLoginName(dr["InceptUserLoginName"].ToString());
                    sectionEmpMsg.SendUser = userDAO.FindByLoginName(dr["SendUserLoginName"].ToString());
                    sectionEmpMsg.MsgContent = dr["MsgContent"].ToString();
                    sectionEmpMsg.ReadState = (int) dr["ReadState"];
                    sectionEmpMsg.SendDateTime = (DateTime) dr["SendDateTime"];
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if(dr != null)
                {
                    dr.Close();
                }
                if(conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return sectionEmpMsg;
        }

        //根据消息编号删除消息记录
        public int delMsgByMsgId(int[] mid, string st)
        {
            int count = 0;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                string sql = "delete from SectionEmpMsgtb where EmpMsgId=@mid";
                if("incept".Equals(st))
                {
                    sql = "delete from SectionEmpMsgtb2 where EmpMsgId=@mid";
                }
                for(int i = 0; i < mid.Length; i++)
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.Add(new SqlParameter("@mid", mid[i]));
                    cmd.ExecuteNonQuery();
                    count++;
                }
                count = mid.Length - count;
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if(conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return count;
        }

        //根据消息编号更改消息状态
        public int updateMsgState(int mid, string st)
        {
            int count = 0;
            SqlConnection conn = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                string sql = "update SectionEmpMsgtb set ReadState = 1 where EmpMsgId = @mid";
                if("incept".Equals(st))
                {
                    sql = "update SectionEmpMsgtb2 set ReadState = 1 where EmpMsgId = @mid";
                }
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@mid", mid));
                count = cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if(conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return count;
        }

        //根据发件人的姓名查询所发消息
        public List<SectionEmpMsgtb> findSendMsgsBySendUserLoginName(string sendUserLoginName)
        {
            List<SectionEmpMsgtb> list = new List<SectionEmpMsgtb>();
            //UserDAO userDAO = new UserDAO();
            SqlConnection conn = null;
            SqlDataReader dr = null;
            try
            {
                conn = SQLManager.GetConn();
                conn.Open();
                //string sql = "select * from SectionEmpMsgtb where SendUserLoginName = @sendUserLoginName order by SendDateTime desc";
                string sql = "select SectionEmpMsgtb.*, usertb.username as 接收人" +
                     " from SectionEmpMsgtb,usertb where SendUserLoginName = @uln and usertb.loginname = sectionEmpMsgtb.InceptUserLoginName" +
                     " order by SendDateTime desc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@uln", sendUserLoginName));
                dr = cmd.ExecuteReader();
                while(dr.Read())
                {
                    SectionEmpMsgtb sectionEmpMsg = new SectionEmpMsgtb();
                    sectionEmpMsg.EmpMsgId = (int) dr["EmpMsgId"];
                    //sectionEmpMsg.InceptUser = userDAO.FindByLoginName(dr["InceptUserLoginName"].ToString());
                    //sectionEmpMsg.SendUser = userDAO.FindByLoginName(dr["SendUserLoginName"].ToString());
                    sectionEmpMsg.MsgContent = dr["MsgContent"].ToString();
                    sectionEmpMsg.ReadState = (int) dr["ReadState"];
                    sectionEmpMsg.SendDateTime = (DateTime) dr["SendDateTime"];
                    sectionEmpMsg.InceptUserName = (string) dr["接收人"];
                    sectionEmpMsg.SendUserName = "-";
                    list.Add(sectionEmpMsg);
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if(dr != null)
                {
                    dr.Close();
                }
                if(conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return list;
        }
    }
}
